capture log close
clear
capture program drop _all
set more off
timer clear 1
timer on 1
set seed 123

**************** 
***  PATHS   ***  
****************
*BCCR: 1 if at Central Bank
global BCCR=1
if $BCCR == 1 {
	global path "Y:/Projects/MNC_Suppliers/Suppliers_Alfaro-Urena_Manelici_Vasquez"
}

if $BCCR == 0 {
	global path "~/Documents/GitHub/Suppliers_Alfaro-Urena_Manelici_Vasquez"
}
cd $path 

*log file
local logfile "${path0}/log_files/5-appendix_b"
set linesize 255
log using `logfile', replace
set linesize 255

/*
RESULTS IN APPENDIX B OF THE PAPER
*/

********************** 
***  INPUT FILES   ***  
**********************
global analysis_data ""processed_data/analysis_data.dta""
global mnc_countries ""raw_data/mnc_countries_sample.dta""
global transactions ""raw_data/trans_clean_corresp.dta""
global revec_group ""raw_data/revec_group.dta""
global mnc_sample ""processed_data/MNC_sample.dta""
global mnc_countries ""raw_data/mnc_countries_sample.dta""

********************************************************************************
* main program: outline of the full code
********************************************************************************
prog main

*Table B1
*Country of Global Ultimate Ownership for the MNCs Triggering the Event
		desc_HQ

*Table B2
*Sectoral Composition of the Sample of First-Time Suppliers and MNCs
		descevent_sectcomp

*Table B3
*Summary Statistics (First-Time Suppliers and Never Suppliers to MNCs)
		desc_suppliers
		
*Table B4
*Characteristics of Amount and Length of Relationship with First MNC Buyer
		amountlengthfirst
		
*Table B5 
		sh_to_other_MNCs
end

********************************************************************************
*Table B1
*Country of Global Ultimate Ownership for the MNCs Triggering the Event
********************************************************************************
prog desc_HQ
display "Country of Global Ultimate Ownership for the MNCs Triggering the Event"
quiet{

*firms in the main regression 
use $analysis_data, clear
reghdfe y D* if past!=1, absorb(year#sector4#province IDs) resid(temp_res)
bys ID: egen res=mean(temp_res)
keep if res!=.
drop if buyer==""
collapse year , by(buyer)
keep buyer
*merging counties 
merge 1:1 buyer using $mnc_countries
keep if _m==3
rename country Country
*table 
tabout Country using ${path0}/results/2-appendix_b/Supplementary_Table_B1.tex, replace c(freq col) f(0c  2c) clab(Freq.\medskip Percent) sort bt style(tex)
}
display "Table B1: Country for the MNCs Triggering the Event"
tab Country, sort
end


********************************************************************************
*Table B2
*Sectoral Composition of the Sample of First-Time Suppliers and MNCs
********************************************************************************
prog descevent_sectcomp
display "table B2 Sectoral Composition: First-Time Suppliers and MNCs"
quiet{
estimates clear 
use $analysis_data, clear
*firms in main sample 
reghdfe y D* if past!=1, absorb(year#sector4#province IDs) resid(temp_res)
bys ID: egen res=mean(temp_res)
keep if res!=.

** Distribution by sector supplier
preserve 
sort ID year
collapse (mean) event, by (ID sector_g)
estpost tab sector_g if event!=., nototal 
est store sup 
restore

** Distribution by sector MNC

sort ID year
collapse (mean) event, by (buyer b_sector_g)
drop if buyer==""
estpost tab b_sector_g, nototal
est store mnc 
}
esttab sup mnc using ${path0}/results/2-appendix_b/Supplementary_Table_B2.tex, replace label varlabels(`e(labels)') cells(pct(fmt(2) label(" "))) mtitles("Suppliers" "MNCs") noobs nonumber 

end

********************************************************************************
*Table B3
*Summary Statistics (First-Time Suppliers and Never Suppliers to MNCs)
********************************************************************************
prog desc_suppliers

************
*the suppliers 
************
quiet{
*import data
use $analysis_data, clear
*length for MNC events
keep ID year
rename ID seller
tempfile seller
save `seller', replace
use $transactions, clear
drop if pot==1
merge m:1 seller year using `seller'
keep if _m==3
drop _m

*main buyer
preserve
gsort seller year -trans_
by seller year: gen main=(_n==1)*trans_
collapse (sum) main, by(year seller)
rename seller ID
tempfile main_buyer
save `main_buyer', replace
restore
*making length comparable to suppliers to MNCs (considering relationships
*starting 2010-)
rename seller ID
bys ID buyer: egen first=min(year)
bys ID: egen max_y=max(year)
keep if first>=2010 | max_y<=2010
bys buyer ID: gen length= _N
bys buyer ID: gen dup=cond(_N==1,0,_n)
drop if dup>1
collapse length , by(ID)
tempfile length
save `length', replace

*merging data
use $analysis_data, clear
merge 1:1 ID year using `main_buyer'
gen sh_main=main/1000000/ingresosir
replace sh_main=0 if sh_main==.
replace sh_main=1 if sh_main>=1
drop _m 
merge m:1 ID using `length'
drop _m 
gen type=(event==.)

*to th dollars 
foreach v in ingresos  {
*approx exchange rate=500
replace `v'=`v'*1000/500
*}
}

*keeping only the observations included in the regressions
reghdfe y  D_*  if past_t!=1 , absorb(year#sector4#province IDs) vce(cluster province#sector_g) resid(temp_res_mnc)
bys ID type: egen res_mnc=min(temp_res_mnc)
keep if res_mnc!=. 

*sales per worker 
gen y_per_l = ingresos/trabaj_

*entry bef or 2005
gen entry_leq_2005=(entry<=2005)

*exporters 
gen exporters=(exports>0 & exports!=.)
*importers
gen importers=(imports>0 & imports!=.)

*broad sector
tab sector_g, gen(sects)

*time invariant characteristics
preserve
bys ID: gen dup=cond(_N==1,0,_n)
drop if dup>1

eststo clear

myttests sects* entry_leq_2005 , by(type) 
distinct ID if type==0
scalar controls=r(ndistinct)
distinct ID if type==1
scalar treated=r(ndistinct)
estadd scalar treated
estadd scalar controls
}
display "column 1: Eventually Treated, column 2: Never Treated"
esttab using ${path0}/results/2-appendix_b/Supplementary_Table_B3.tex , nomtitle nonumbers  ///
    cells("mu_1(fmt(a3)) mu_2" ///
	"mu_1_se(par fmt(2)) mu_2_se(par fmt(2))" ) ///
	collabels("First-time Suppliers" "Never Suppliers") ///
	stats(controls  treated , fmt(0 0) labels("\# Treated Firms" "\# Control Firms" )) label ///
	replace type note("column 1: Eventually Treated, column 2: Never Treated")
restore

quiet{
*time varying characteristics (averaged) bef event 
global variab "ingresos trabaj_ y_per_l importers exporters n_clients length sh_main"
keep if time<=0 | event==. 
eststo clear
myttests  $variab , by(type) 
distinct ID if type==0
scalar controls=r(ndistinct)
distinct ID if type==1
scalar treated=r(ndistinct)
estadd scalar treated
estadd scalar controls

}
display "column 1: Eventually Treated, column 2: Never Treated"
esttab using ${path0}/results/2-appendix_b/Supplementary_Table_B3.tex , nomtitle nonumbers  ///
    cells("mu_1(fmt(a3)) mu_2" ///
	"mu_1_se(par fmt(2)) mu_2_se(par fmt(2))" ) ///
	collabels("First-time Suppliers" "Never Suppliers") ///
	stats(controls  treated , fmt(0 0) labels("\# Control Firms" "\# Treated Firms" )) label ///
	append type note("column 1: Eventually Treated, column 2: Never Treated")
end

********************************************************************************
*Table B4
*Characteristics of Amount and Length of Relationship with First MNC Buyer
********************************************************************************
prog amountlengthfirst
display "table B4: Characteristics of Amount and Length of Relationship with First MNC Buyer"
quiet{
estimates clear
*firms in main sample 
use $analysis_data, clear
reghdfe y D* if past!=1, absorb(year#sector4#province IDs) resid(temp_res)
bys ID: egen res=mean(temp_res)
keep if res!=.
*trans in USD (500 exchange rate)
replace trans_first_mnc=trans_first_mnc/500
bys ID year: gen active=1 if trans_first_mnc>0 & trans_first_mnc!=.

bys ID year: gen activeall=1 if tot_trans_mnc>0 & tot_trans_mnc!=.

sort ID activeall year 
by ID activeall: gen spell=_n if tot_trans_mnc!=0
by ID activeall: gen spelltot=_N if tot_trans_mnc!=0
gen eventyear=spell-1
gen otherclients=n_fore_clients

preserve
keep if year==event
collapse (mean) trans_first_mnc, by (ID)
sum trans_first_mnc
replace trans_first_mnc=trans_first_mnc*1000 
label var trans_first_mnc "First transaction with MNC (thousands of US\\$)"
}
display "First transaction with MNC (thousands of USD)"
estpost tabstat trans_first_mnc, stat(count mean p50 sd)
esttab using ${path0}/results/2-appendix_b/Supplementary_Table_B4.tex, replace label cells("count(label(\textit{N}) fmt(0)) mean(label(Mean) fmt(2)) p50(label(Median) fmt(2) ) sd(label(S.D.) fmt(2))")  refcat(trans_first_mnc "Variable \medskip", nolabel) nonum noobs f booktabs
restore

**Length
sort ID year
preserve
keep if active==1
collapse (sum) active, by (ID)
label var active "Length of relationship with first MNC"
display "Length of relationship with first MNC"
estpost tabstat active, stat(count mean p50 sd)  
esttab using ${path0}/results/2-appendix_b/Supplementary_Table_B4.tex, append label cells("count(fmt(0)) mean(fmt(2)) p50(fmt(2) ) sd(fmt(2))") nonum noobs collabels(none) f plain booktabs nogaps
restore

**Length all MNC

sort ID year
preserve
keep if activeall==1
collapse (sum) activeall, by (ID)
label var activeall "Length of relationship with all MNCs"
display "Length of relationship with all MNCs"
estpost tabstat activeall, stat(count mean p50 sd)
esttab using ${path0}/results/2-appendix_b/Supplementary_Table_B4.tex, append label cells("count(fmt(0)) mean(fmt(2)) p50(fmt(2) ) sd(fmt(2))")  nonum noobs collabels(none) f plain booktabs nogaps
restore


end

********************************************************************************
* Table B5 
* Split of Corporate Sales by Old or New, Domestic or MNC Buyers
********************************************************************************
prog sh_to_other_MNCs
display "share to other buyers (old and new, domestic and MNCs)"
quiet{
*NEW/OLD SUPPLIERS, SECTORS, ETC
estimates clear
use $analysis_data, clear

*  	DUMMIES
*dummies to show in tables  	
ds D_m4-D_4
global dummies `r(varlist)'
*in the main sample 
reghdfe y D* if past!=1, absorb(year#province#sector4 IDs) resid(temp_res)
bys ID: egen res=mean(temp_res)
replace res=(res!=.)

keep event ingresos costo_de_ imports past province sector* ID year y res D* IDs

*sellers
preserve
keep if res==1
rename * s_*
rename s_ID seller
rename s_year year
tempfile as_seller
save `as_seller', replace
restore 

*buyers
drop D*
rename * b_*
rename b_ID buyer
rename b_year year
tempfile as_buyer
save `as_buyer', replace

* MNCS LIST
use $mnc_sample, clear
keep ID
rename ID buyer
tempfile MNC_buyer
save `MNC_buyer', replace
rename buyer seller
tempfile MNC_seller
save `MNC_seller', replace

* TRANSACTIONS
use $transactions, clear
drop if pot==1
drop pot
*REAL VALUE
local trans_vari "trans"
foreach v of local trans_vari{
forvalue i=2008/2017{
replace `v'=(100*(`v')/${ipc`i'})/1000000 if year==`i'
}
}

* MERGING BUYERS AND SELLERS
merge m:1 buyer year using `as_buyer'
bys buyer: egen buyer_data=max(_m)
replace buyer_data=(buyer_data==3)
drop _m
merge m:1 seller year using `as_seller'
bys seller: egen seller_data=max(_m)
replace seller_data=(seller_data==3)
drop _m
keep if buyer_data==1 | seller_data==1
* WHICH ARE MNCS
merge m:1 buyer using `MNC_buyer'
gen buyer_mnc=(_m==3)
drop if _m==2
drop _m

* buyers that triggered the event
gen trig=(year==s_event & buyer_mnc==1)
bys seller buyer: egen mnc_trig=max(trig)
gen tr_not_event=trans_*(1-mnc_trig)
gen l_trans_not_event=log(1000*tr_not_event)
egen sellers=group(seller)
egen buyers=group(buyer)
gen time= year-s_event

*old buyers
gen temp_old_b=(time<=0) 
bys buyer seller: egen old_b=max(temp_old_b)
drop temp*
scalar last_year=4
scalar first_year=-4
gen time_reg=time
replace time_reg=`=last_year' if time>=`=last_year' & time!=.
replace time_reg=`=first_year' if time<=`=first_year'
 }
***
*** figure with percentages depending on buyer types and table
***
quiet{
preserve
gen buyer_type_1= (old_b==1 & mnc_trig!=1)
gen buyer_type_2= (mnc_trig==1)
gen buyer_type_3= (old_b==0 & buyer_mnc!=1)
gen buyer_type_4= (buyer_mnc==1 & mnc_trig!=1)
assert buyer_type_1 + buyer_type_2 + buyer_type_3 + buyer_type_4 ==1
forvalue i=1/4{
gen n_buyer_type_`i'=buyer_type_`i'
replace buyer_type_`i'=buyer_type_`i'*trans_
}
*total trans
bys seller year: egen tot_trans=total(trans_)
collapse (sum) n_buyer_type_* buyer_type_* (mean) tot_trans s_ingresos , by(seller year time)
forvalue i=1/4{
gen sh_buyer_type_`i'=buyer_type_`i'/s_ingresos
replace sh_buyer_type_`i'=1 if sh_buyer_type_`i'>1 & sh_buyer_type_`i'!=.
}
gen time_reg=time
replace time_reg=. if time<0 | time>4
label def sa 0"0" 1"+1" 2"+2" 3"+3" 4"+4" 
label val time_reg sa
estpost tabstat  sh_buyer_type_2, statistics(count mean sd) by(time_reg) nototal  
est store share1
estpost tabstat  sh_buyer_type_4, statistics (count mean sd) by(time_reg) nototal  
est store share2
estpost tabstat  n_buyer_type_4, statistics(count mean sd) by(time_reg) nototal  
est store n_others
}
esttab share1 share2 n_others using ${path0}/results/2-appendix_b/Supplementary_Table_B5.tex, wide cells("count(label(\textit{N}) fmt(0)) mean(label(Mean) fmt(2)) sd(label(SD) fmt(2))")  replace label mtitles("Share sold to first MNC"  "Share sold to other MNCs"  "Number of other MNC buyers") nonum noobs type
restore

end


********************************************************************************
* PROGRAM TO GENERATE DIFF IN MEANS
********************************************************************************
program myttests, eclass
     syntax varlist [if] [in], by(varname) [ * ]
     marksample touse
     markout `touse' `by'
	 local varia_program "mu_1 mu_2 n_1 n_2 mu_1_se mu_2_se d d_se d_t d_p"
     tempname `varia_program'
      foreach var of local varlist {
         qui ttest `var' if `touse', by(`by') `options'
         mat `mu_1' = nullmat(`mu_1'), r(mu_1)
         mat `mu_2' = nullmat(`mu_2'), r(mu_2)
         mat `n_1' = nullmat(`n_1'), r(N_1)
         mat `n_2' = nullmat(`n_2'), r(N_2)		 
         mat `mu_1_se' = nullmat(`mu_1_se'), r(sd_1)
         mat `mu_2_se' = nullmat(`mu_2_se'), r(sd_2)		 
         mat `d'    = nullmat(`d'   ), r(mu_1)-r(mu_2)
         mat `d_se' = nullmat(`d_se'), r(se)
         mat `d_t'  = nullmat(`d_t' ), r(t)
         mat `d_p'  = nullmat(`d_p' ), r(p)
     }
         foreach mat in `varia_program' {
         mat coln ``mat'' = `varlist'
     }
     tempname b V
     mat `b' = `mu_1'*0
     mat `V' = `b''*`b'
     eret post `b' `V'
     eret local cmd "myttests"
     foreach mat in `varia_program' {
        eret mat `mat' = ``mat''
    }
 end
********************************************************************************
*run regressiona and stores relevant info
********************************************************************************
prog run_reg
capture drop resid
capture drop dep

*regression 
$REG resid(resid) 

* add number of observations 
scalar n_obs=e(N)
estadd scalar n_obs

* add distinct number of firms
distinct IDs if resid!=.
scalar n_firms=r(ndistinct)
estadd scalar n_firms 

* add mean and sd of dependent variable 
global dep_var "`e(depvar)'"
if "$quant" == ""{
*in US dollars
gen dep=exp( $dep_var )/500
}
if "$quant" == "1" {
gen dep=exp( $dep_var )
}
global quant ""
quietly summ dep if resid!=.
scalar mean_dep=r(mean)
scalar sd_dep=r(sd)
estadd scalar mean_dep
estadd scalar sd_dep

*add number of fixed effects 
scalar n_fe=e(df_a_initial)
estadd scalar n_fe

capture drop resid
capture drop dep
end

quiet{
***  PRODUCER PRICE INDEX  (yearly average) 2013 ==100   
global ipc2005 = 50.99*100/92.88
global ipc2006 = 57.96*100/92.88
global ipc2007 = 63.38*100/92.88
global ipc2008 = 75.43*100/92.88
global ipc2009 = 77.53*100/92.88
global ipc2010 = 81.15*100/92.88
global ipc2011 = 85.57*100/92.88
global ipc2012 = 89.78*100/92.88
global ipc2013 = 92.88*100/92.88
global ipc2014 = 98.99*100/92.88
global ipc2015 = 98.93*100/92.88
global ipc2016 = 100.08*100/92.88
global ipc2017 = 100.77*100/92.88
}

********************************************************************************
*PROGRAM TO ADD FIXED EFFECTS INDICATOR TO LATEX TABLES           
********************************************************************************
program addfe
    if `"`0'"'=="" local 0 _fe
    tempname b V
    mat `b' = 0
    mat coln `b' = `0'
    mat `b' = e(b), `b'
    mat `V' = e(V)
    mat `V' = (`V', J(rowsof(`V'), 1, 0)) \ (J(1, colsof(`V'), 0), 0)
    erepost b=`b' V=`V', rename
end

******************************************************************************** 
***  EXECUTE THE PROGRAM main   
********************************************************************************
main

******************************************************************************** 
***  TIMER
********************************************************************************
timer off 1
*time in seconds
timer list 1
*time in minutes
local time_ =round(`r(t1)'/60)
display "the code takes `time_' minutes in total"

log close
translate `logfile'.smcl `logfile'.txt , replace linesize(250)
capture erase `logfile'.smcl
